package com.LostAndFound.dao;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.LostAndFound.domain.SystemInfo;
import com.LostAndFound.utils.DataSourceUtils;

public class WelcomeDao {
	
	/*select i.infoid,i.title,i.infoDetail,i.datetime,r.username from info i,root r where i.rid=r.rid order by datetime desc limit 0,8;*/
	public List<Map<String, Object>> showSystemInfo() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select i.infoid,i.title,i.infoDetail,i.datetime,r.username,r.rid "
				+ " from info i,root r where i.rid=r.rid "
				+ " order by datetime desc limit 0,8 ";
		return runner.query(sql, new MapListHandler());
	}

	public SystemInfo showInfoDetail(String infoid) throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select * from info where infoid = ?";
		return runner.query(sql, new BeanHandler<SystemInfo>(SystemInfo.class), infoid);
	}
	
	// 得到多少条寻找失物记录要进行审核
	public int getTotalSearchLoseToShow() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 1 and invalidflag = 2 and foundflag = 2";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}
	// 得到多少条寻找失主记录要进行审核
	public int getTotalSearchLoserToShow() throws SQLException {
		QueryRunner runner = new QueryRunner(DataSourceUtils.getDataSource());
		String sql = "select count(*) from article "
				+ " where whatflag = 0 and invalidflag = 2 and foundflag = 2";
		Long total = (Long) runner.query(sql, new ScalarHandler());
		return total.intValue();
	}
}
